#!/usr/bin/env python
#!encoding:utf-8
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import xlrd
from pymongo import MongoClient
import time, datetime


class Excel:
    """docs"""
    def __init__(self, xls_file, *args, **kwargs):
        self.bk = Excel.workbook(xls_file, *args, **kwargs)
        self._sheet = None

    @staticmethod
    def workbook(xls_file, *args, **kwargs):
        try:
            return xlrd.open_workbook(xls_file, *args, **kwargs)
        except Exception as e:
            print e.message
            sys.exit()

    @property
    def book(self):
        return self.bk

    @property
    def data(self):
        return self._sheet

    def show_sheets(self):
        return self.bk.sheet_names()

    def get_sheet(self, name=0):
        if isinstance(name, int):
            return self.bk.sheet_by_index(name)
        if isinstance(name, str):
            return self.bk.sheet_by_name(name)
        else:
            print 'No sheet got'
            return None

    def get_row_data(self, sh, rowx, colrange=None, block=10):
        result = []
        dmode = self.bk.datemode
        ctys = sh.row_types(rowx)
        cvals = sh.row_values(rowx)
        if not colrange:
            colrange = xrange(sh.row_len(rowx))
        for colx in colrange:
            cty = ctys[colx]
            cval = cvals[colx]
            if self.bk.formatting_info:
                cxfx = str(sh.cell_xf_index(rowx, colx))
            else:
                cxfx = ''
            if cty == xlrd.XL_CELL_DATE:
                try:
                    showval = xlrd.xldate_as_tuple(cval, dmode)
                except xlrd.XLDateError as e:
                    showval = "%s:%s" % (type(e).__name__, e)
                    cty = xlrd.XL_CELL_ERROR
            elif cty == xlrd.XL_CELL_ERROR:
                showval = xlrd.error_text_from_code.get(cval, '<Unknown error code 0x%02x>' % cval)
            else:
                showval = cval
            result.append((colx, cty, showval, cxfx))
        return result

    def show_row(self, sh, rowx, printit=True):
        #if self.bk.ragged_rows:
        #    colrange = range(sh.row_len(rowx))
        colrange = range(sh.row_len(rowx))
        if not colrange: return
        #if printit: print()
        if self.bk.formatting_info:
            for colx, ty, val, cxfx in self.get_row_data(sh, rowx, colrange):
                if printit:
                    print("cell %s%d: type=%d, data: %s, xfx: %s"
                        % (xlrd.colname(colx), rowx+1, ty, val, cxfx))
        else:
            for colx, ty, val, _unused in self.get_row_data(sh, rowx, colrange):
                if printit:
                    print("cell %s%d: type=%d, data: %s" % (xlrd.colname(colx), rowx+1, ty, val))

    def get_sheet_data(self, sh):
        data = {}
        rows, cols = sh.nrows, sh.ncols
        colrange = range(cols)
        keys = [val for colx, ty, val, cxfx in self.get_row_data(sh, 0)]

        data['SheetName'] = sh.name
        data['Keys'] = keys
        data['Data'] = []
        for rowx in xrange(1, rows):
            values = [val for colx, ty, val, cxfx in self.get_row_data(sh, rowx)]
            data['Data'].append(values)
        return data

    def row_iter(self, sh):
        rows, cols = sh.nrows, sh.ncols
        colrange = range(cols)

        for rowx in xrange(1, rows):
            row = [val for colx, ty, val, cxfx in self.get_row_data(sh, rowx)]
            yield row

    def fromat_mongo(self, sh,
        keys=['pro_name', 'pro_scale', 'pro_price_total', 'pro_price_one'],
        other_items={}):
        result = []
        data = self.get_sheet_data(sh)
        keys = keys or data['Keys']
        other_items.update({'category': sh.name})
        _ = [dict(zip(keys, values)) for values in data['Data']]
        for d in _:
            d.update(other_items)
            result.append(d)
        return result

    def format_csv(self, sh, key=None, other_items=None):
        pass


class Workdaystat(Excel):

    def __init__(self, book_name, sheet_name=None, *args, **kwargs):
        Excel.__init__(self, book_name, *args, **kwargs)
        self._sheet = self.get_sheet(sheet_name) if sheet_name else self.get_sheet(0)
        self.sheet_data_iter = self.row_iter(self._sheet)
        self._data = []

    def _time(self, timestr):
        hour, minute, second = (0, 0, 0)
        if not timestr:
            return (0, 0, 0)
        ret = timestr.split(':')
        if len(ret) >= 3:
            hour, minute, second = ret[:3]
        elif len(ret) == 2:
            hour, minute = ret
        else:
            hour = ret
        return int(hour), int(minute), int(second)


    def _date(self, datestr, formatstr='YY:MM:DD', sep=None):
        _format = formatstr.upper().split(':')
        if sep:
            ret = datestr.split(sep)
        else:
            for sp in ['-', '/']:
                if sp in datestr:
                    ret = datestr.split(sp)

        year = ret[_format.index('YY')]
        month = ret[_fomat.index('MM')]
        day = ret[_format.index('DD')]

        return year, month, day

    def _to_datetime(self, date_time):
        date_time = tuple(date_time)
        #year, month, day, hour, minute, second = (2015, 1, 1, 0, 0, 0)
        year, month, day, hour, minute, second = date_time
        return datetime.datetime(year, month, day, hour, minute, second)


    def stat(self):
        #self.show_row(self._sheet, 19)
        
        while True:
            try:
                row =  self.sheet_data_iter.next()
            except StopIteration:
                break
            date, time1, time2 = row[:3]
            if not date:
                break
            time1 = self._time(time1)
            time2 = self._time(time2)
            time1 = tuple(time1)
            time2 = tuple(time2)
            dtime1 = date[:3] + time1
            dtime2 = date[:3] + time2

            print date[:3], time1, time2, self._to_datetime(dtime2)-self._to_datetime(dtime1)


if __name__ == '__main__':
    # '''
    # bk = Excel('items.xls')
    # db = MongoClient('localhost', 27017).web
    # for b in bk.book.sheets():

    #     if b.name == 'Sheet1':
    #         continue
    #     print b.name 
    #     db.products.insert(bk.fromat_mongo(b))
    # '''
    # #fname = sys.args[1]
    b = Workdaystat('bbrBURBERRY.xlsx')
    for i in b.sheet_data_iter:
        print i
